﻿CREATE TRIGGER [dbo].[triu_Upload]
ON [dbo].[t_Upload]
FOR INSERT,UPDATE
AS

IF @@ROWCOUNT = 0 RETURN
IF TRIGGER_NESTLEVEL (@@PROCID) > 1 RETURN
SET NOCOUNT ON

IF NOT EXISTS(SELECT * FROM t_UploadDecision UD INNER JOIN inserted i ON UD.UploadID = i.ID)
	INSERT INTO t_UploadDecision(UploadID)SELECT ID FROM inserted

UPDATE U SET
	Serial# = LTRIM(RTRIM(i.Serial#)),
	[Primary SSN] = dbo.clr_RegEx_Replace(i.[Primary SSN],'[^0-9]','',-1,0,1)
FROM t_Upload U INNER JOIN inserted i ON i.ID = U.ID
WHERE dbo.clr_RegEx_Match(ISNULL(U.[Primary SSN],''),'[^0-9]',1) > '' OR
	U.Serial# <> LTRIM(RTRIM(i.Serial#))

UPDATE U SET
	APR = i.APR * 100
FROM t_Upload U INNER JOIN inserted i ON i.ID = U.ID
WHERE U.APR < 1

